﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using ICSharpCode.SharpZipLib;
using System.Reflection;
using System.IO;


namespace STHPMS1.Common.C_Excel
{
    public class ExcelTool
    {
        private static string GetHeader(string[] headers)
        {
            if (headers == null) return "";
            string temp = "";
            foreach (string s in headers)
            {
                temp += "[ " + s + " ]";
            }
            return temp;
        }
        public static void ValidationColumn(string fileName, string type)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                sheet = workbook.GetSheetAt(0);
                string[] headers = null;// { "学号", "姓名", "年龄", "班级" };
                if (type == "pzd")
                {
                    headers = new string[] { "起点桩号", "终点桩号", "调查日期", "检测人员", "检测设备", "IRI", "注释", "导入时间" };
                }
                else if (type == "cz")
                {
                    headers = new string[] { "起点桩号", "终点桩号", "调查日期", "检测设备", "检测人员", "外道车辙", "内道车辙", "RD", "注释", "导入时间" };
                }
                if (headers != null)
                {
                    for (int i = 0; i < headers.Length; i++)
                    {
                        IRow row = sheet.GetRow(0);//第一行
                        ICell cell = row.GetCell(i);
                        if (!(cell.StringCellValue == (headers[i])))
                        {

                            throw new Exception("表头与模板不一致,模板列为" + GetHeader(headers));
                        }
                    }
                }
            }

            finally
            {
                //sheet.Dispose();  
                workbook = null;
                sheet = null;
            }
        }

        /// <summary>  
        /// 从Excel中获取数据到DataTable(不读取方向工作簿)  
        /// </summary>  
        /// <param name="fileName">文件路径</param>  
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>  
        /// <returns></returns>  
        public static DataTable RenderDataTableFromExcel(string fileName, int HeaderRowIndex)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;

            DataTable table = new DataTable();
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                sheet = workbook.GetSheetAt(0);
                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(i.ToString());
                    table.Columns.Add(column);
                }

                int rowCount = sheet.LastRowNum;


                #region 循环各行各列,写入数据到DataTable
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            //dataRow[j] = cell.ToString();  
                            switch (cell.CellType)
                            {
                                case CellType.Blank://空数据类型
                                    dataRow[j] = "";
                                    break;
                                case CellType.Boolean:
                                    dataRow[j] = cell.BooleanCellValue;
                                    break;
                                case CellType.Numeric:
                                    //dataRow[j] = cell.NumericCellValue;
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                    {
                                        dataRow[j] = cell.DateCellValue;
                                    }
                                    else//其他数字类型
                                    {
                                        dataRow[j] = cell.NumericCellValue;
                                    }
                                    break;
                                case CellType.String:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                case CellType.Error:
                                    dataRow[j] = cell.ErrorCellValue;
                                    break;
                                case CellType.Formula://公式类型
                                default:
                                    dataRow[j] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                    //dataRow[j] = row.GetCell(j).ToString();  
                }
                #endregion
            }
            catch (Exception ex)
            {
                throw new Exception("Excel数据读取错误");
            }
            finally
            {
                //sheet.Dispose();  
                workbook = null;
                sheet = null;
            }
            //清除最后的空行
            for (int i = table.Rows.Count - 1; i > 0; i--)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j] != null)
                    {
                        if (table.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
                {
                    table.Rows[i].Delete();
                }
            }
            return table;
        }

        /// <summary>
        /// 从指定工作簿中读取数据
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="HeaderRowIndex"></param>
        /// <param name="sheetIndex">工作簿下标</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcelBySheet(string fileName, int HeaderRowIndex, int sheetIndex)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;

            DataTable table = new DataTable();
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                sheet = workbook.GetSheetAt(sheetIndex);
                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                //for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                //{
                //    DataColumn column = new DataColumn(i.ToString());
                //    table.Columns.Add(column);
                //} 
                table.Columns.Add(new DataColumn("路线编号"));
                table.Columns.Add(new DataColumn("起点桩号"));
                table.Columns.Add(new DataColumn("终点桩号"));
                 if (sheet.SheetName == "是否全幅")
                {
                    table.Columns.Add(new DataColumn("是否全幅"));
                }
                else if (sheet.SheetName == "行政区域")
                {
                    table.Columns.Add(new DataColumn("行政区域"));
                }
                else if (sheet.SheetName == "管养单位")
                {
                    table.Columns.Add(new DataColumn("管养单位"));
                }
                else if (sheet.SheetName == "技术等级")
                {
                    table.Columns.Add(new DataColumn("技术等级"));
                }
                else if (sheet.SheetName == "车道数")
                {
                    table.Columns.Add(new DataColumn("车道数"));
                }
                else if (sheet.SheetName == "路面宽度")
                {
                    table.Columns.Add(new DataColumn("路面宽度"));
                }
                else if (sheet.SheetName == "通车时间")
                {
                    table.Columns.Add(new DataColumn("通车时间"));
                }
                else if (sheet.SheetName == "土基类型")
                {
                    table.Columns.Add(new DataColumn("土基类型"));
                }
                else if (sheet.SheetName == "路肩类型")
                {
                    table.Columns.Add(new DataColumn("路肩类型"));
                }
                 else if (sheet.SheetName == "交通量")
                 {
                     table.Columns.Add(new DataColumn("交通量"));
                 }
                int rowCount = sheet.LastRowNum;


                #region 循环各行各列,写入数据到DataTable
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            //dataRow[j] = cell.ToString();  
                            switch (cell.CellType)
                            {
                                case CellType.Blank://空数据类型
                                    dataRow[j] = "";
                                    break;
                                case CellType.Boolean:
                                    dataRow[j] = cell.BooleanCellValue;
                                    break;
                                case CellType.Numeric:
                                    //dataRow[j] = cell.NumericCellValue;
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                    {
                                        dataRow[j] = cell.DateCellValue;
                                    }
                                    else//其他数字类型
                                    {
                                        dataRow[j] = cell.NumericCellValue;
                                    }
                                    break;
                                case CellType.String:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                case CellType.Error:
                                    dataRow[j] = cell.ErrorCellValue;
                                    break;
                                case CellType.Formula://公式类型
                                default:
                                    dataRow[j] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                    //dataRow[j] = row.GetCell(j).ToString();  
                }
                #endregion
            }
            catch (Exception ex)
            {
                throw new Exception("Excel数据读取错误");
            }
            finally
            {
                //sheet.Dispose();  
                workbook = null;
                sheet = null;
            }
            //清除最后的空行
            for (int i = table.Rows.Count - 1; i > 0; i--)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j] != null)
                    {
                        if (table.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
                {
                    table.Rows[i].Delete();
                }
            }
            return table;
        }


        /// <summary>  
        /// 从Excel中获取数据到DataTable(读取方向工作簿)  
        /// </summary>  
        /// <param name="fileName">文件路径</param>  
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>  
        /// <returns></returns>  
        public static DataTable GetDataTableFromExcel(string fileName, int HeaderRowIndex)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;

            DataTable table = new DataTable();
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);
                int sheetCount = workbook.NumberOfSheets;//得到所有的sheet总数
                //遍历Excel的工作簿
                for (int k = 0; k < sheetCount; k++)
                {
                    sheet = workbook.GetSheetAt(k); //第几个工作簿
                    IRow headerRow = sheet.GetRow(HeaderRowIndex);//列头 
                    int cellCount = headerRow.LastCellNum;//列头的单元格输量

                    if (table.Columns.Count == 0) //初始化DataTable的列
                    {
                        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                        {
                            DataColumn column = new DataColumn(i.ToString());
                            table.Columns.Add(column);
                        }
                        //保存方向
                        table.Columns.Add(new DataColumn("dir"));
                    }

                    #region 循环各行各列,写入数据到DataTable
                    for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++) //循环行
                    {
                        IRow row = sheet.GetRow(i);
                        //判断该行第一列是否为空
                        if (row == null || row.GetCell(0) == null)
                        {
                            break;
                        }
                        DataRow dataRow = table.NewRow();

                        dataRow[table.Columns.Count - 1] = sheet.SheetName;//保存调查方向
                        for (int j = row.FirstCellNum; j < cellCount; j++) //循环列
                        {
                            ICell cell = row.GetCell(j);
                            if (cell == null)
                            {
                                dataRow[j] = null;
                            }
                            else
                            {
                                //dataRow[j] = cell.ToString();  
                                switch (cell.CellType)
                                {
                                    case CellType.Blank://空数据类型
                                        dataRow[j] = "";
                                        break;
                                    case CellType.Boolean:
                                        dataRow[j] = cell.BooleanCellValue;
                                        break;
                                    case CellType.Numeric:
                                        //dataRow[j] = cell.NumericCellValue;
                                        if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                        {
                                            dataRow[j] = cell.DateCellValue;
                                        }
                                        else//其他数字类型
                                        {
                                            dataRow[j] = cell.NumericCellValue;
                                        }
                                        break;
                                    case CellType.String:
                                        dataRow[j] = cell.StringCellValue;
                                        break;
                                    case CellType.Error:
                                        dataRow[j] = cell.ErrorCellValue;
                                        break;
                                    case CellType.Formula://公式类型
                                    default:
                                        dataRow[j] = "=" + cell.CellFormula;
                                        break;
                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    #endregion
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Excel数据读取错误");
            }
            finally
            {
                //sheet.Dispose();  
                workbook = null;
                sheet = null;
            }
            //清除最后的空行
            for (int i = table.Rows.Count - 1; i >= 0; i--)
            {
                if (table.Rows[i][0].ToString().Trim()=="")
                {
                    table.Rows[i].Delete();
                }
            }
            return table;
        }
        /*
        public DataTable GetExcelToData(string fileName, int startRow)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
           
            DataTable dataExcel = new DataTable();//单元格类的DataTable
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                int sheetCount = workbook.NumberOfSheets;//得到所有的sheet总数
                //对工作表循环
                for (int sIndex = 0; sIndex < sheetCount; sIndex++)
                {
                    sheet = workbook.GetSheetAt(sIndex);
                    if (sheet != null)
                    {
                        //最后一列的标号
                        int rowCount = sheet.LastRowNum;
                        IRow rowHead = sheet.GetRow(startRow-1);
                        //单元格列数
                        int cellCount = rowHead.LastCellNum;

                        for (int i = startRow; i < rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);//单元格行
                            if (row == null) continue; //没有数据的行默认是null　
                            if (row.GetCell(0) == null) continue; //第1列为空
                            if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim() == "") continue;//没有桩号名称
                            if (row.GetCell(2).ToString().ToLower().Contains("sum")) { continue; }//过滤统计行
                            DataRow dataRow = dataExcel.NewRow(); //单元格行
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                {

                                    if (j == 0 || j == 1)
                                    {
                                        dataRow[j] = row.GetCell(j).NumericCellValue / (double)1000;
                                    }
                                    else
                                    {
                                        dataRow[j] = row.GetCell(j).NumericCellValue; //包含公式,只取值
                                    }

                                }

                            }
                            //对dataRow解析,存到entityRow
                            foreach (DataColumn c in dataExcel.Columns)
                            {
                                entityRow[c.ColumnName] = dataRow[c.ColumnName];
                            }
                            entityRow["pmanskiid"] = Guid.NewGuid().ToString();
                            entityRow["lineid"] = pmanski.lineid;
                            entityRow["svydrt"] = pmanski.svydrt;
                            entityRow["svydate"] = pmanski.svydate;
                            entityRow["svyps"] = pmanski.svyps;


                            //保存可能出错的sheet和行数
                            entityRow["ErrorInfo"] = workbook.GetSheetName(sIndex) + "_" + (i + 1);
                            dataEntity.Rows.Add(entityRow);
                        }
                    }
                }
                //排序
                return dataEntity;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
         */
    }
}
